# -*- coding: utf-8 -*-

#连接操作Mysql

import re
import sys
import time
import traceback

import pandas as pd
import pymysql as MySQLdb
#import MySQLdb
sys.path.append('..')

class mysql_connecter(object):
    def __init__(self,db_cfg):
        self._connect = None
        self._cursor = None
        self._cursor_dic = None
        self.con_stat = False
        
        self.dbconfig = db_cfg
        if not self.dbconfig or not self.dbconfig_test(db_cfg):
            # ailog.info('=== MYSQL读取配置文件失败')
            return None

        self.con_stat = self.db_init()
        
        #test
        # print('=== 读取的Mysql库为：%s %s'%(self.dbconfig['host'],self.dbconfig['db']))
        #test
        
    def __del__(self):
        self.close_db()


    def dbconfig_test(self, dbconfig):
        flag = True
        if type(dbconfig) is not dict:
            print('dbconfig is not dict')
            flag = False
        else:
            for key in ['host', 'port', 'user', 'passwd', 'db']:
                if not key in dbconfig:
                    print("dbconfig error: do not have %s" % key)
                    flag = False
            if not 'charset' in dbconfig:
                self.dbconfig['charset'] = 'utf8'

        if not flag:
            raise Exception('Dbconfig Error')
        return flag


    #数据库初始化
    def db_init(self):
        try:
            self._connect = MySQLdb.connect(
                host=self.dbconfig['host'],
                port=int(self.dbconfig['port']),
                user=self.dbconfig['user'],
                passwd=self.dbconfig['passwd'],
                db=self.dbconfig['db'],
                charset=self.dbconfig['charset'],
                connect_timeout=10)
        
            self._cursor = self._connect.cursor()
            self._cursor_dic = self._connect.cursor(MySQLdb.cursors.DictCursor)
            
            self.set_transaction_isolation_read_committed()
            
            #设置自动提交
            #self._connect.autocommit(True)
            
        except Exception as e:
            self._connect = None
            self._cursor = None
            print("DB init error:", e)
            return False  
        
        return True
        
    def close_db(self):
        try:
            if self._connect is not None:
                self._cursor_dic.close()
                self._cursor.close()
                self._connect.close()

                self._cursor_dic = None
                self._cursor = None
                self._connect = None
                self.con_stat = False
        except Exception as e:
            traceback.print_exc()
            print('WARN: close db failed,',e)

    #检查连接
    #因pymysql无MySQLdb 的stat接口，因此报错，本方法遗弃
    def check_connect_old(self):
        try:
            res = self._connect.stat()
            msg="MySQL server has gone away"
            if res == msg:
                print(msg)
                self._cursor_dic.close()
                self._cursor.close()
                self._connect.close()
                return False
            
            return True
        except Exception as e:
            print('#', e)
            self._cursor_dic.close()
            self._cursor.close()
            self._connect.close()            
            return False
     
    def check_connect(self):
        if self._connect is None:
            print('#MySQL 未连接。')
            return False

        flag=True
        try:
            #判断是否为长连接2006异常
            self._connect.ping()
        except:
            print('#MySQL 链接异常。')
            self._cursor_dic.close()
            self._cursor.close()
            self._connect.close()
            flag=False

        return flag

    #检查连接,若连接失败重连
    def check_reconnect(self, reset=True):
        for _ in range(6):
            if self.check_connect():
                if reset:
                    #清空缓存，防止脏读
                    self.reset_connect_cache()
                return True
            else:
                print('MySQL reconnect ...')
                self.db_init()
                if self.check_connect():
                   return True

            time.sleep(5)

        return False 
    
    #事物开始
    def transaction_begin(self):
        #self._connect.begin()
        self._cursor.execute('begin')
    
    #提交commit
    def transaction_commit(self):
        #self._connect.commit()
        self._cursor.execute('commit')
    
    #回滚
    def transaction_rollback(self):
        self._connect.rollback() 
    
    def set_innodb_lock_waitout(self,waitout=1):
        sql = 'SET innodb_lock_wait_timeout=1 '
        self._cursor.execute(sql)
    
    def set_transaction_isolation_read_committed(self):
        sql = 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED'
        self._cursor.execute(sql)
    
    def show_tx_isolation(self):
        sql = 'select @@tx_isolation;'
        data = self.query(sql,'one')
        print(data)

    def reset_connect_cache(self):
        #sql = '''reset query cache'''
        #self._cursor.execute(sql)
        self._connect.commit()

    
    def query(self, sql, ret_type='all'):
        import ailog
        try:
            if ret_type == 'all_dict':
                query_cursor = self._cursor_dic
            else:
                query_cursor = self._cursor
            query_cursor.execute("SET NAMES utf8")
            #print(sql)
            # ailog.debug('sql : {}'.format(sql))
            query_cursor.execute(sql)
            if ret_type == 'all':
                return query_cursor.fetchall()
            elif ret_type == 'all_mlp':
                return query_cursor.fetchall(), query_cursor.__dict__.get("description")            
            elif ret_type == 'one':
                return query_cursor.fetchone()
            elif ret_type == 'count':
                return query_cursor.rowcount
            elif ret_type == 'all_dict':
                return query_cursor.fetchall()            
        except Exception as e:
            ailog.error("Mysql query error: {}  sql :{}".format(e, sql))
            ailog.save_error_log(job_id=0, job_type=-1, error_code='900007', key_word='{}'.format(e))
            raise e

    #批量插入
    def do_insertmany(self, sql, param, commit=True):
        '''use executemany do insert'''
        try:       
            self._cursor.execute("SET NAMES utf8")
            self._cursor.executemany(sql, param)
            ins_id = self._connect.insert_id()
            if commit:
                self._connect.commit()
            
            return ins_id
        
        except Exception as e:
            print("Mysql insertmany error:",e)
            print(sql)
            if commit:
                self._connect.rollback()
            return e

    # 批量更新
    def do_updatemany(self, sql, param, commit=True):
        '''use executemany do update'''
        try:
            self._cursor.execute("SET NAMES utf8")
            self._cursor.executemany(sql, param)
            if commit:
                self._connect.commit()

        except Exception as e:
            print("Mysql updatemany error:", e)
            print(sql)
            if commit:
                self._connect.rollback()
            return e

    # 新增
    def update_timestamp(self, sql, commit=True):
        try:
            self._cursor.execute("SET NAMES utf8")
            self._cursor.execute(sql)
            if commit:
                self._connect.commit()

        except Exception as e:
            print("Mysql updatemany error:", e)
            print(sql)
            if commit:
                self._connect.rollback()
            return e

    def dml(self, sql, commit=True):
        import ailog
        '''update or delete or insert'''
        try:
            self._cursor.execute("SET NAMES utf8")
            self._cursor.execute(sql)

            m_type = self.dml_type(sql)
            # if primary key is auto increase, return inserted ID.
            if m_type == 'insert':
                res = self._connect.insert_id()
            else:
                res = True

            if commit:
                self._connect.commit()

            return res
        except Exception as e:
            #ailog.error("Mysql dml error: {}  sql :{}".format(e,sql))
            print("Mysql dml error: {}  sql :{}".format(e,sql))
            # ailog.save_error_log(job_id=0,job_type=-1,error_code='900007',key_word='{}'.format(e))
            raise e

    def dml_type(self, sql):
        re_dml = re.compile('^(?P<dml>\w+)\s+', re.I)
        m = re_dml.match(sql)
        if m:
            if m.group("dml").lower() == 'delete':
                return 'delete'
            elif m.group("dml").lower() == 'update':
                return 'update'
            elif m.group("dml").lower() == 'insert':
                return 'insert'
            elif m.group("dml").lower() == 'create':
                return 'create'
        print("%s --- Warning: '%s' is not dml." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())), sql))
        return False


    def check_table_exist(self, db_name, table_name):
        sql="select table_name from information_schema.`TABLES` where table_name='%s' and table_schema='%s'" \
            % (table_name, db_name)
        
        count = self.query(sql, 'count')
        
        if count > 0:
            return True
        else:
            return False

    def get_table_count(self, table, cond=None):
        if cond is None:
            sql = "select count(*) from %s" % table
        else:
            sql = "select count(*) from %s %s" % (table, cond)
        try:
            count = self.query(sql, 'one')
        except:
            print('Get table [%s] count Failed.' % table)
            count = 0
        
        assert count is not False  #sql failed
            
        return int(count[0])

    def get_table_column_length(self,table,column_name):
        db=self.dbconfig['db']
        sql = 'select character_maximum_length from information_schema.COLUMNS'
        sql+=' where table_schema="%s" and table_name="%s"'%(db,table)
        sql+=' and column_name="%s"'%(column_name)
        try:
            data = self.query(sql,'one')
            if data and len(data)>0:
                return data[0]
            else:
                return None
        except Exception as e:
            print(sql)
            print("Mysql get_table_column_length error:", e)
            return None

    def pandas_read_sql(self, sql):
        try:
            return pd.read_sql(sql, con=self._connect)

        except Exception as e:
            print(sql)
            traceback.print_exc()
            raise e
            return None

    def pandas_to_sql(self, pandas_inst, table_name, if_exists='append', index=False, index_label=None, chunksize=1000):
        from sqlalchemy import create_engine
        try:
            stmt = 'mysql://%s:%s@%s:%s/%s?charset=utf8' % (self.dbconfig['user'],
                                               self.dbconfig['passwd'],
                                               self.dbconfig['host'],
                                               self.dbconfig['port'],
                                               self.dbconfig['db'])
            #print(self.dbconfig['charset'])
            engine = create_engine(stmt, encoding='utf-8')
            pandas_inst.to_sql(table_name,con=engine,if_exists=if_exists,index=index,index_label=index_label,chunksize=chunksize)
            return True
        except Exception as e:
            traceback.print_exc()
            raise e
            return False
        finally:
            if engine:
                engine.dispose()

    def db_escape_string(self, t_str):
        #return MySQLdb.escape_string(t_str)
        return self._connect.escape_string(t_str)

if __name__ == '__main__':
    dbconfig = {'host':'192.168.35.126',
            'port':3306,
            'user':'nlp',
            'passwd':'nlp@2019',
            'db':'contract_db_online',
            'charset':'utf8'}
    mysql_oper = mysql_connecter(dbconfig)
    sql_str='select * from t_contract where contract_code="HNCSA1804342CGN00"'
    data=mysql_oper.query(sql_str)
    print(data)
    print('============')
    data1=mysql_oper.query(sql_str,'all_dict')
    print(data1)
    print('============')
    print(mysql_oper.query(sql_str,'one'))
    print(mysql_oper.query(sql_str,'count'))
    print(mysql_oper.query(sql_str,'all_mlp'))
    print('============')
    column_len = mysql_oper.get_table_column_length('t_contract','contract_code')
    print(column_len)
    print('============')
    print(mysql_oper.pandas_read_sql(sql_str))
    print("=========")
    data_dict={'id':[1,2],'name':['aa','bb'],'score':[10,9]}
    df=pd.DataFrame(data_dict)
    mysql_oper.pandas_to_sql(df,'cyc_test')
    print('===over===')

